<?php 
$req_path="./";
include_once($req_path."include/ini.php");
require_once($req_path."include/db.php");

class M_customer extends DB{
	private $dbConn;
	private $rowCount="0";
	private $ID="0";
	private $sqlSelect =" select * from tb_customer where 1=1 and IsDeleted=0 ";
	
	function M_customer(){
		parent::__construct();
	}
	function getListPage(){
		global $list_page;
		return $list_page;
	}

	function getLastRunning($code){
		$sql ="select value from tb_running where name='".$code."'";
		
		$result = $this->ExecuteData($sql);
		
		$sqlupdate="update tb_running set value=value+1 where name='".$code."'";
		$this->ExecuteData($sqlupdate);
		
		$row=$result->FetchRow();
		
		return $row["value"]+1;
	}
	function getUserLogin(){
		global $user;
		return $user;
	}
	function getData( $name=null,$code=null,$birthdayfrom=null,$birthdayto=null,$lastfrom=null,$lastto=null ,$pageStart=1, $cardid=null ,$lastname=null){
		global  $list_page;
		$sqlscript= "select ID, Code, FirstName ,coalesce(NickName,'') NickName , coalesce(LastName,'&nbsp;') LastName,coalesce(Telephone,'&nbsp;') as Telephone 
					,  case when coalesce(DATE_FORMAT( BirthDay , '%d/%m/%Y' ),'')='00/00/0000' or BirthDay is NULL then '' else  CONCAT(Year( FROM_DAYS(DATEDIFF( NOW( ) , BirthDay ))),'y ',month( FROM_DAYS(DATEDIFF( NOW( ) , BirthDay ))),'m ',DAY( FROM_DAYS(DATEDIFF( NOW( ) , BirthDay ))),'d') end  BirthDay
					 , coalesce(DATE_FORMAT( LastedAccess , '%d/%m/%Y' ),'&nbsp;') LastedAccess 
					 , coalesce(DATE_FORMAT( CreatedDate , '%d/%m/%Y' ),'&nbsp;') CreatedDate
					  , coalesce(DATE_FORMAT( UpdatedDate , '%d/%m/%Y' ),'') UpdatedDate
					 , Email,Sex , CardID   from tb_customer where 1=1 and IsDeleted=0 ";
		
		if (isset($name) && $name!=""){
			$sqlscript.= " and (FirstName like '%". $name."%')";
		}
			if (isset($lastname) && $lastname!="") {
			$sqlscript.= " and (LastName like '%". $lastname."%' )";	
			
		}
		if (isset($code) && $code!=""){
			$sqlscript.= " and Code ='". $code."'";
		}
		if (isset($birthdayfrom) && $birthdayfrom!=""){
			list($day, $month, $year) = split('/', $birthdayfrom);
			$bfrom= $year."/".$month."/".$day;
			
			list($day, $month, $year) = split('/', $birthdayto);
			$bTo=$year."/".$month."/".$day;
			
			$sqlscript.= " and BirthDay Between'". $bfrom."'  and '".$bTo."'";
		}

		if (isset($cardid) && $cardid!=""){
			$sqlscript.= " and CardID LIKE '%". $cardid."%'";
		}

		if (isset($lastfrom) && $lastfrom!=""){
			list($day, $month, $year) = split('/', $lastfrom);
			$bfrom= $year."/".$month."/".$day;
			
			list($day, $month, $year) = split('/', $lastto);
			$bTo=$year."/".$month."/".$day;
			$sqlscript.= " and LastedAccess Between'". $bfrom."'  and '".$bTo."'";		
		}
		//Order 
		$sqlscript.= " order by ID asc " ;
		//Execute
		$result = $this->ExecuteData($sqlscript);
		//Get all rows
		$rows =$result->RecordCount();
	
		//Page limit
		$sqlscript.= " limit ". ($pageStart-1)*$list_page. ", " . $list_page;
		//paging
		$result = $this->ExecuteData($sqlscript);	
	
		$totalpage=1;
		if ($rows>0){
			$rt = $rows%$list_page;
			
			if($rt!=0) {
				$totalpage = floor($rows/$list_page)+1;
			}
			else {
				$totalpage = floor($rows/$list_page);
			}
		}
		
		$this->rowCount=$rows;
		$this->totalpage=$totalpage;	
		
		return $result;	 
	}
	function getData2( $name=null,$code=null,$birthdayfrom=null,$birthdayto=null,$lastfrom=null,$lastto=null ,$pageStart=1 , $cardid=null ){
		global  $list_page;
		$sqlscript= " select c.ID, c.Code, c.FirstName ,coalesce(NickName,'') NickName , coalesce(c.LastName,'&nbsp;') LastName,coalesce(c.Telephone,'&nbsp;') as Telephone 
					,  case when coalesce(DATE_FORMAT( BirthDay , '%d/%m/%Y' ),'')='00/00/0000' or BirthDay is NULL then '' else  CONCAT(Year( FROM_DAYS(DATEDIFF( NOW( ) ,BirthDay ))),'y ',month( FROM_DAYS(DATEDIFF( NOW( ) ,BirthDay ))),'m ',DAY( FROM_DAYS(DATEDIFF( NOW( ) , BirthDay ))),'d') end  BirthDay
					 , coalesce(DATE_FORMAT( c.LastedAccess , '%d/%m/%Y' ),'&nbsp;') LastedAccess 
					 , coalesce(DATE_FORMAT( c.CreatedDate , '%d/%m/%Y' ),'&nbsp;') CreatedDate
					 
					 , c.Email,c.Sex ,c.CardID  
					  from tb_customer c 
					  where 1=1 and c.IsDeleted=0 ";
		
		if (isset($name) && $name!=""){
			$sqlscript.= " and (c.FirstName like '%". $name."%')";
		}
		

		if (isset($code) && $code!=""){
			$sqlscript.= " and c.Code ='". $code."'";
		}
		if (isset($birthdayfrom) && $birthdayfrom!=""){
			list($day, $month, $year) = split('/', $birthdayfrom);
			$bfrom= $year."/".$month."/".$day;
			
			list($day, $month, $year) = split('/', $birthdayto);
			$bTo=$year."/".$month."/".$day;
			
			$sqlscript.= " and c.BirthDay Between'". $bfrom."'  and '".$bTo."'";
		}
		if (isset($lastfrom) && $lastfrom!=""){
			list($day, $month, $year) = split('/', $lastfrom);
			$bfrom= $year."/".$month."/".$day;
			
			list($day, $month, $year) = split('/', $lastto);
			$bTo=$year."/".$month."/".$day;
			$sqlscript.= " and c.LastedAccess Between'". $bfrom."'  and '".$bTo."'";		
		}

		if (isset($cardid) && $cardid!=""){
			$sqlscript.= " and c.CardID LIKE '%". $cardid."%'";
		}

		//Order 
		$sqlscript.= " order by c.ID asc " ;
		//Execute
		$result = $this->ExecuteData($sqlscript);
		//Get all rows
		$rows =$result->RecordCount();
	
		//Page limit
		//$sqlscript.= " limit ". ($pageStart-1)*$list_page. ", " . $list_page;
		//paging
		$result = $this->ExecuteData($sqlscript);	
	
		$totalpage=1;
		if ($rows>0){
			$rt = $rows%$list_page;
			
			if($rt!=0) {
				$totalpage = floor($rows/$list_page)+1;
			}
			else {
				$totalpage = floor($rows/$list_page);
			}
		}
		
		$this->rowCount=$rows;
		$this->totalpage=$totalpage;	
		
		return $result;	 
	}
	function getDataDetail($id=0){
		$sqlscript= "select ID, Code, FirstName  ,coalesce(NickName,'') NickName, Address, coalesce(LastName,'') LastName,coalesce(Telephone,'&nbsp;') as Telephone
					,  case when coalesce(DATE_FORMAT( BirthDay , '%d/%m/%Y' ),'')='00/00/0000' or BirthDay is NULL then '' else  CONCAT(Year( FROM_DAYS(DATEDIFF( NOW( ) ,BirthDay ))),'y ',month( FROM_DAYS(DATEDIFF( NOW( ) ,BirthDay ))),'m ',DAY( FROM_DAYS(DATEDIFF( NOW( ) , BirthDay ))),'d') end  BirthDay
					 , coalesce(DATE_FORMAT( CreatedDate , '%d/%m/%Y' ),'') CreatedDate
					 , coalesce(DATE_FORMAT( LastedAccess , '%d/%m/%Y' ),'&nbsp;') LastedAccess , Email , Sex , CardID 
					  from tb_customer where 1=1 and IsDeleted=0 and ID='".$id."'";

		$result = $this->ExecuteData($sqlscript);
		return $result->FetchRow();
	}

	function checkCustomerCode($code){
		$sql="select * from tb_customer where Code='".$code."'";
		
		$result=$this->ExecuteData($sql);
		$rows=$result->RecordCount();
		$val=$rows > 0? "0":"1";
		return $val;
	}
	
	function getRowCount(){
		return $this->rowCount;
	}
	
	function getLastInsert(){
		return $this->ID;
	}
	
	function InsertData($val){
		if (isset($val)){
			$sqlInsert=$this->InsertSqlScript($this->sqlSelect, $val);
			//$this->ExecuteData($sqlInsert);
			//$this->ID = $this->ExecuteDataAndGetID($sqlInsert);
			//$this->ID = $this->getLastID();
			return  $this->ExecuteDataAndGetID($sqlInsert);
		}
	}
	function updateData($sql, $val){
		if(isset($val)){
			$sqlUpdate=$this->UpdateSqlScript($sql,$val);
			return $this->ExecuteData($sqlUpdate);
		}
	}
	function delData($id){
		if(isset($id)){
			$sqlDelete = "update tb_customer set IsDeleted=1 where ID='".$id."'";
			$this->ExecuteData($sqlDelete);
			return "c";
		}
	}
	function getTotalPage(){
		return $this->totalpage;
	}
	function getCustomerSmartPurse($id=0){
		$sqlScript="select DATE_FORMAT( s.CreatedDate , '%d/%m/%Y' ) HistoryDate ,"
					." CardNo , Amount , s.ID "
					." from tb_customer c "
					." left join tb_smartpurse s on s.CustID=c.ID where s.CustID=".$id
					. "  and s.IsDeleted=0 "
					." order by s.CreatedDate desc ";
		$result = $this->ExecuteData($sqlScript);
		return $result;		
	}
	function InsertSmartPurse($val){
		if (isset($val)){
			$sqlInsert=$this->InsertSqlScript("select * from tb_smartpurse Where 1=1", $val);
			$this->ExecuteData($sqlInsert);
			$this->ID = $this->getLastID();
			return $this->ID;
		}
	}
	function delSmartPurse($id){
		$sql="Update  tb_smartpurse set  IsDeleted=1 Where ID=".$id;
		$this->ExecuteData($sql);
	}
}
?>